var myDB = require('./myDB');

/*
 所有的函数的参数都是只有2个:pm,fn;pm就是参数,fn就是执行完毕后的回调函数,回调函数的格式都是fn(ok,data)
 pm是个{}json对象,即便只有一个参数也是,比如delById({id:0}),虽然稍微麻烦,但是也只有delById和delByIds是只有一个参数,所以不予考虑
 到了具体数据表的dao中可以简化使用,使用普通参数
 非循环的6个函数（快速），名称全部后面加上 E 或者前面加上 _

 存在业务逻辑的肯定有:delById,delByIds,delByWhere,editById,editByIds,editByWhere,add,adds,addsCsv,addNumById,addNumByIds,addNumByWhere
 但是,实际上会比较少,因为:
 1.ByWhere的一般不直接使用,而是修改为,比如delByNodeId,
 2.edit也会修改为edit和passed,而passed才有业务
 3.adds也是,一定会是需要配置好nodeId,才要批量添加
 所以,dao只提供正常数据访问的,而不提供业务逻辑的循环
 业务全部放到具体的,比如userDao中,如果是这样,那么dao,最好是全部静态的
 还有就是不会使用到继承的问题
 其实,就是全部废弃了循环的用法,全面提升性能,
 而每个具体Dao中,都需要加入权限限制,只提供应该使用到的函数,
 */


var myDao = module.exports = {
    //提供默认的dbInfo,主要是用于测试
    dbInfo: {
        dbType: 'mysql',
        host: 'localhost',
        port: 3306,
        user: 'root',
        password: 'root',
        database: 'test',
        table: 'user'
    }



}

/**
 * 可以执行非常复杂的sql语句及批量语句
 * @param dbInfo
 * @param sqls
 * @param pms
 * @param fn
 */
myDao.exeSqls = function (dbInfo, sqls, pms, fn) {
    dbInfo = dbInfo || this.dbInfo;   //提供默认的dbInfo,主要是用于测试
    myDB.exeSqls(dbInfo, sqls, pms, fn);
}


/*
 //把mydb中得exeQuery的返回值做一些修改,如果返回值为空,则算到错误中,表示没有数据,同时加一个参数,是否是一行数据,或者唯一的值,直接返回对应的值
 pm.id:
 pm.keys:
 rtn:row
 */
myDao.getById = function (dbInfo, pm, fn) {
    dbInfo = dbInfo || this.dbInfo;   //提供默认的dbInfo,主要是用于测试
    var p = getPbyPm_id(pm);

    myDao.getByWhere(dbInfo, p, function (err, rows) {
        if (!err) {
            if (rows.length > 0) {
                fn(err, rows[0]);
            } else {
                fn(err, []);
            }
        } else {
            fn(err, rows);
        }

    });
}


/*
 pm.ids:
 pm.keys:
 pm.order:
 rtn:
 */
myDao.getByIds = function (dbInfo, pm, fn) {
    dbInfo = dbInfo || this.dbInfo;   //提供默认的dbInfo,主要是用于测试

    var p = getPbyPm_ids(pm);

    myDao.getByWhere(dbInfo, p, fn);
}


/*
 pm.keys:
 pm.where:
 pm.pms:
 pm.order:
 pm.pageIndex:
 pm.pageSize:
 rtn:

 */

//如果where是个数组，那么第一条是sql语句，后面的都是pms
//如果where是个对象，那么有sql和pms两个属性，无法和整体对象区分
myDao.getByWhere = function (dbInfo, pm, fn) {
    dbInfo = dbInfo || this.dbInfo;   //提供默认的dbInfo,主要是用于测试

    var p = pm || {};
    var sql = getSelect(dbInfo.table, p.keys) + getWhere(p.where) + getOrder(p.order) + getPage(p.pageIndex, p.pageSize);
    myDao.exeSqls(dbInfo, sql, p.pms, fn);
}


/*
 pm.id:
 rtn:
 */
myDao.delById = function (dbInfo, pm, fn) {
    dbInfo = dbInfo || this.dbInfo;   //提供默认的dbInfo,主要是用于测试
    var p = getPbyPm_id(pm);

    myDao.delByWhere(dbInfo, p, fn);
}


/*
 pm.ids:
 rtn:
 */
myDao.delByIds = function (dbInfo, pm, fn) {
    dbInfo = dbInfo || this.dbInfo;   //提供默认的dbInfo,主要是用于测试

    var p = getPbyPm_ids(pm);
    myDao.delByWhere(dbInfo, p, fn);
}


/*
 pm.where:
 pm.pms:
 rtn:
 */
myDao.delByWhere = function (dbInfo, pm, fn) {
    dbInfo = dbInfo || this.dbInfo;   //提供默认的dbInfo,主要是用于测试

    var p = pm || {};
    var sql = getDel(dbInfo.table) + getWhere(p.where);
    myDao.exeSqls(dbInfo, sql, p.pms, fn);
}

/*
 pm.row:
 pm.id:
 rtn:
 */
myDao.editById = function (dbInfo, pm, fn) {
    dbInfo = dbInfo || this.dbInfo;   //提供默认的dbInfo,主要是用于测试
    var p = getPbyPm_id(pm);
    myDao.editByWhere(dbInfo, p, fn);
}


/*
 pm.row:
 pm.ids:
 rtn:
 */
myDao.editByIds = function (dbInfo, pm, fn) {
    dbInfo = dbInfo || this.dbInfo;   //提供默认的dbInfo,主要是用于测试
    var p = getPbyPm_ids(pm);
    myDao.editByWhere(dbInfo, p, fn);
}

/*
 pm.row:
 pm.where:
 pm.pms:
 rtn:
 */
myDao.editByWhere = function (dbInfo, pm, fn) {
    dbInfo = dbInfo || this.dbInfo;   //提供默认的dbInfo,主要是用于测试
    var p = pm || {};
    var r = getUpdateByObj(p.row);
    var sql = getUpdate(dbInfo.table) + r.sql + getWhere(p.where);
    var pms = r.pms.concat(p.pms);
    myDao.exeSqls(dbInfo, sql, pms, fn);
}

/*
 pm.row:
 rtn:
 */
myDao.add = function (dbInfo, pm, fn) {
    dbInfo = dbInfo || this.dbInfo;   //提供默认的dbInfo,主要是用于测试
    var p = pm || {};
    var r = getInsertByObj(p.row);
    var sql = getInsert(dbInfo.table) + r.sql;
    myDao.exeSqls(dbInfo, sql, r.pms, fn);
}

/*
 pm.rows:
 rtn:
 */
myDao.adds = function (dbInfo, pm, fn) {
    dbInfo = dbInfo || this.dbInfo;   //提供默认的dbInfo,主要是用于测试
    var p = pm || {};
    var r = getInsertByObjs(p.rows);
    var sql = getInsert(dbInfo.table) + r.sql;
    myDao.exeSqls(dbInfo, sql, r.pms, fn);
}

/*
 pm.keys:
 pm.cvs:数组的数组,而rows是对象的数组
 rtn:
 */
myDao.addsByCsv = function (dbInfo, pm, fn) {
    dbInfo = dbInfo || this.dbInfo;   //提供默认的dbInfo,主要是用于测试
    var p = pm || {};
    var r = getInsertByArrays(p.keys, p.rows);
    var sql = getInsert(dbInfo.table) + r.sql;
    myDao.exeSqls(dbInfo, sql, r.pms, fn);
}

// select count(*) as total,sum(age) as sum_age from user
/*
 pm.keys		: count(*) as total,sum(age) as sum_age
 pm.where
 pm.pms
 rtn:
 */
myDao.getCountSumByWhere = function (dbInfo, pm, fn) {
    dbInfo = dbInfo || this.dbInfo;   //提供默认的dbInfo,主要是用于测试
    var p = pm || {};
    var sql = getCountSums(dbInfo.table, p.keys) + getWhere(p.where);
    myDao.exeSqls(dbInfo, sql, p.pms, fn);
}


/*
 pm.id:
 pm.keys:
 pm.fn:function(key){return key}
 rtn:尚未完成
 */
myDao.editFnByWhere = function (dbInfo, pm, fn) {
    dbInfo = dbInfo || this.dbInfo;   //提供默认的dbInfo,主要是用于测试

    pm = pm || {};
    var p = {};
    p.where = "id=?";
//    p.pms = pm.id.split(",");
    p.pms = [];
    p.pms.push(pm.id);
    p.keys = pm.keys;
    p.num = pm.num;
    myDao.addNumByWhere(dbInfo, p, fn);

}


/*
 pm.id:
 pm.row:
 rtn:
 */
myDao.addNumById = function (dbInfo, pm, fn) {
    dbInfo = dbInfo || this.dbInfo;   //提供默认的dbInfo,主要是用于测试
    var p = getPbyPm_id(pm);
    myDao.addNumByWhere(dbInfo, p, fn);
}

/*
 pm.ids:
 pm.row:
 rtn:
 */
myDao.addNumByIds = function (dbInfo, pm, fn) {
    dbInfo = dbInfo || this.dbInfo;   //提供默认的dbInfo,主要是用于测试
    var p = getPbyPm_ids(pm);
    myDao.addNumByWhere(dbInfo, p, fn);
}
//pm 	: 	where,pms,fields,num
/*
 pm.where
 pm.pms
 pm.row
 rtn:
 */
myDao.addNumByWhere = function (dbInfo, pm, fn) {
    dbInfo = dbInfo || this.dbInfo;   //提供默认的dbInfo,主要是用于测试
    var p = pm || {};
    var r = getUpdateByObj(p.row, true);
    var sql = getUpdate(dbInfo.table) + r.sql + getWhere(p.where);
    var pms = r.pms.concat(p.pms);
    myDao.exeSqls(dbInfo, sql, pms, fn);
}


//////////////////////////////////////////////////////////////


myDao.createTable = function (dbInfo, pm, fn) {
    dbInfo = dbInfo || this.dbInfo;   //提供默认的dbInfo,主要是用于测试

    pm = pm || {};
    var sqls = getCreate(dbInfo.table, pm);
    myDao.exeSqls(dbInfo, sqls, fn)
}

//////////////////////////////////////////////////////////////
//drop table if exists people;create table people (id integer primary key autoincrement ,name not null unique,sex bit,age,money);
function getCreate(table, tableInfo) {
    var sqls = "drop table if exists " + table;
    sqls += ";create table " + table + " (id integer primary key autoincrement";
    for (var key in tableInfo) {
        if (key != 'id') {
            sqls += "," + key + " " + tableInfo[key].dbType;
        }
    }
    sqls += ");"
    return sqls;
}


//默认keys  = * ,表示所有字段
function getSelect(table, keys) {
    var fs = keys || ' * ';
    return "select " + fs + " from " + table;
}

function getUpdate(table) {
    return "update " + table;
}


function getDel(table) {
    return "delete from " + table;
}


function getInsert(table) {
    return "insert  into " + table;
}


// select count(*) as total,sum(age) as sum_age from user
function getCountSums(table, countSums) {
    var cs = countSums || " count(*)  as total ";
    return " select  " + cs + " from " + table;
}

//把一个对象数组，转换为嵌套数组(值数组)+keys(字段名数组)
function getArraysByObjs(objs) {
    var keys = []; //表头,字段名数组
    var valuess = []; //数组的数组

    for (var key in objs[0]) {
        keys.push(key); //从第一个对象中获取对象的名称列表
    }
    //循环objs,获取每个对象
    for (var i in objs) {
        var obj = objs[i]; //对象
        var values = []; //对象转换成的数组
        for (var j in keys) {
            var key = keys[j]
            values.push(obj[key])
        }
        valuess.push(values);
    }
    return {
        keys: keys,
        valuess: valuess
    };
}

//把一个嵌套数组(值数组)+字段名数组，转换为对象数组
function getObjsByArrays(arrays, keys) {
    var objs = [];
    for (var i in arrays) {
        var obj = {};
        var arr = arrays[i];
        for (var j in arr) {
            var key = keys[j];
            obj[key] = arr[j];
        }
        objs.push(obj);
    }
    return objs;
}


//名称列表，值列表数组（多行数据）
function getInsertByObjs(objs) {
    var o = getArraysByObjs(objs);
    return getInsertByArrays(o.keys, o.valuess);
}

//返回一个数组,所有值都是一个默认值
function getArray_DefVal(len, value) {
    var rtn = [len];
    for (var i = 0; i < len; i++) {
        rtn[i] = value;
    }
    return rtn;
}


//名称列表，值列表数组（多行数据）
function getInsertByArrays(keys, valuess) {
    var rtn = {
        sql: "",
        pms: ""
    };
    var v = getArray_DefVal(keys.length, "?");

    rtn.sql = " (" + keys.toString() + ") values(" + v.toString() + ") ";
    rtn.pms = valuess;
    return rtn;
}

//insert into tableName (field1,field2) values (v1,v2) 
function getInsertByObj(obj) {
    var rtn = {
        sql: "",
        pms: ""
    };
    var a = getArrayByObject(obj);
    var v = getArray_DefVal(a.keys.length, "?");

    rtn.sql = " (" + a.keys.toString() + ") values(" + v.toString() + ") ";
    rtn.pms = a.values;
    return rtn;
}


// update from tableName set  aName=aValue,bName=bValue where id>0
//add 	:  如果add=true,表示在原基础上增加多少数值,或者在后面连接多少字符串
function getUpdateByObj(obj, add) {
    var rtn = {
        sql: " set ",
        pms: []
    };
    var a = getArrayByObject(obj);
    var sql = "";
    for (var i in a.keys) {
        var key = a.keys[i];
        if (add) {
            sql += key + "=" + key + "+?";
        } else {
            sql += key + "=?";
        }
    }

    rtn.sql += sql;
    rtn.pms = a.values;
    return rtn;
}


//把一个obj转换为2个数组，名称数组，值数组
function getArrayByObject(obj) {
    var rtn = {
        keys: [],
        values: []
    };
    for (var key in obj) {
        rtn.keys.push(key);
        rtn.values.push(obj[key]);
    }
    return rtn;
}


// 默认值是 "" ,就是没有条件,就是查询所有记录
function getWhere(where) {
    if (where) {
        return " where " + where;
    } else {
        return "";
    }
}

//默认值是: id desc
function getOrder(order) {
    if (order) {
        return " order by " + order + " ,id desc ";
    } else {
        return " order by id desc ";
    }
}

//默认值: pageIndex:0,pageSize:10
function getPage(pageIndex, pageSize) {
    var size = pageSize || 10;
    var index = pageIndex || 0;
    var start = index * size;
    return " limit " + start + "," + size;
}


//把pm中应该转换成where的,转换到p
function getPbyPm_ids(pm) {
    pm = pm || {};
    var p = objCopy(pm, 'ids');
    p.pms = pm.ids.split(",");   //实际上是应该转换为数字类型的数组,不过使用字符串数组也不出错误
    var ids = getArray_DefVal(p.pms.length, '?');
    p.where = 'id in (' + ids.toString() + ')';
    return p;
}

//把pm中应该转换成where的,转换到p
function getPbyPm_id(pm) {
    pm = pm || {};
    var p = objCopy(pm, 'id');
    p.where = "id=?";
    p.pms = [];
    p.pms.push(pm.id);
    return p;
}
//除了id属性(或者ids)以为都复制
function objCopy(obj, prop) {
    var newobj = {};
    for (var property in obj) {
        var a = obj[property];
        if ((typeof(a) != 'function') && property != prop) {
            newobj[property] = a;
        }
    }
    return newobj;
}